use master
go

create database AtmSystemon on
(
	Name='AtmSystem',
	FileName='D:\bank\AtmSystem.mdf',
	Size=5MB,
	MaxSize=150MB,
	FileGrowth=5MB
)
log on
(
	Name='AtmSystem_log',
	FileName='D:\bank\AtmSystem_log.ldf',
	Size=5MB,
	MaxSize=150MB,
	FileGrowth=15%
)
go

use AtmSystem
go

create table userInfo
(
	customerID int primary key identity,
	customerName nvarchar(10) not null,
	PID varchar(18) not null unique check(len(PID)=18 or len(PID)=15),
	telephone varchar(20) not null check(telephone like '____-________'),
	addressInfo nvarchar(80)
)
go

create table cardInfo
(
	cardID varchar(50) primary key check(cardID like '1010 3576 ____ ___') default('1010 3576 '+ right('0000' + cast(floor(rand()*10000) as varchar),4) 
    + ' ' + right('000' + cast(floor(rand()*1000) as varchar),3)),
	curType varchar(20) not null default('RMB'),
	savingType nvarchar(4) check(savingType in ('活期', '定活两期', '定期')),
	openDate date not null default(getdate()),
	balance money not null check(balance>=1),
	pass int check(len(pass) = 6) not null default(888888),
	IsReportLoss nchar(1) not null default('否') check(IsReportLoss in ('是','否')),
	customerID int references userInfo(customerID)
)
go


create table transInfo
(
	transId int primary key identity,
	transDate datetime not null default(getdate()),
	cardID varchar(50) references cardInfo(cardID),
	transType nchar(2) not null check(transType in ('存入','支取')),
	transMoney money not null check(transMoney > 0),
	remark text
)
go

-- insert message from userInfo to SandBonze MonkeySun TangMonk
insert into userInfo values
('孙悟空','123456789012345','0716-78989783','北京海淀'),
('沙和尚','421345678912345678','0478-44223333',null),
('唐僧','321245678912345678','0478-44443333',null)
go

-- insert message from cardInfo to many card_Message
insert into cardInfo values
('1010 3576 1234 567',default,'活期',default,1000,default,default,1),
('1010 3576 1212 117',default,'定期',default,1,default,default,2),
('1010 3576 1212 113',default,'定期',default,1,default,default,3)
go

-- 1.update 611234 to password from MonkeySun
update cardInfo set pass = 611234 where customerID = '孙悟空'
go

-- 2.two sentence for take money reduce 2 hundred to MonkeySun 
begin transaction
insert into transInfo(cardID, transType, transMoney) values('1010 3576 1234 567', '支取', 200)
update cardInfo set balance = balance - 200 where cardID = (select cardID from cardInfo where customerID = 1)
commit
go

-- 3.give SandBonze 3 hundred to get off the way
begin transaction
insert into transInfo(cardID, transType, transMoney) values('1010 3576 1234 567', '存入', 300)
update cardInfo set balance = balance + 300 where cardID = (select cardID from cardInfo where customerID = 2)
commit
go

-- 4.TangMonk lost his card need report it (why he don't die? lose money to have live?)
update cardInfo set IsReportLoss = '是' where cardID = (select cardID from cardInfo where customerID = 3)
go

-- 5.select from ten days all cards message 
-- 1
select
	*
from
	cardInfo
where
	datediff(dd,openDate,GETDATE()) <= 10

-- 2
select
	*
from
	cardInfo
where
	DATEADD(dd,-10,GETDATE()) <= openDate

-- 6.select the most deal account to use son select
select
	* 
from
	cardInfo c
join
	transInfo t
on
	c.cardID = t.cardID
where
	t.transMoney = 
	(
		select
			max(transMoney)
		from
			transInfo
	)

-- 7.再交易信息表中，将总的交易金额，支取的交易金额，存入的交易金额查询出来并输出显示
select '总交易金额:', sum(transMoney) from transInfo
union
select '支取交易金额:', transMoney from transInfo where transType = '支取'
union
select '支取交易金额:', transMoney from transInfo where transType = '存入'

-- 8.balance less than 200 to display cardID, customerID
select
	cardID, customerID
from
	cardInfo
where
	balance < 200